package testExcel;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Snowflake;
import com.alibaba.fastjson.*;
import entity.temp.MyData;
import entity.temp.Nesting;
import org.apache.http.entity.ContentType;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;
import utils.fileConvert.ExcelUtil;

import java.io.*;
import java.util.*;

/**
 * 读取Excel, 并作转换成结构化数据的类
 * @author Chen Yuan
 * @create 2023-05-17  9:31
 */
public class ReadExcelTest {
	private static Snowflake snowflake = new Snowflake(1, 1);

	public static void main(String[] args) {

		try {
			String path = "C:\\Users\\c9346\\Desktop\\test.xlsx";
			File file = new File(path);
			FileInputStream fileInputStream = new FileInputStream(file);
			MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);

			List<JSONObject> jsonObjects = ExcelUtil.readExcByFile(multipartFile, JSONObject.class, 1);
			//jsonObjects.forEach(System.out::println);

			// Excel 内容转 嵌套关系
			//List<Nesting> nesting = readExcelTest.createNesting(results);
			//System.out.println(JSON.toJSONString(nesting));

			// 生成字典SQL
			//List<String> sqls = readExcelTest.createDictSQLByNesting(nesting);
			//sqls.forEach(System.out::println);

			// 生成普通SQL
			List<String> sqls = ReadExcelTest.createTableSQL(jsonObjects);
			sqls.forEach(System.out::println);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	// 生成新增SQL
	private static List<String> createTableSQL(List<JSONObject> list) {
		List<String> result = new ArrayList<>();
		StringBuilder builder;
		String commont, field, type;

		for (JSONObject object : list) {
			field = object.getString("field").replaceAll("\\s", "");
			commont = object.getString("name").replaceAll("\\s", "")
					+ ","
					+ object.getString("decs");
			type = object.getString("type");

			builder = new StringBuilder();
			builder.append("`").append(field).append("` ")
					.append(type).append(" ")
					.append("COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ")
					.append("'").append(commont).append("'")
					.append(",");
			result.add(builder.toString());
		}
		return result;
	}

	// 生成新增SQL
	private static List<String> createSQL(List<JSONObject> list) {
		String pre = "INSERT INTO `sys_dict` (`dict_id`, `dict_type_id`, `code`, `name`, `parent_id`, `parent_ids`, `status`, `sort`, `create_time`, `update_time`) VALUES (";
		String suf = ");";
		String dictName = "name";
		String dictCodePre = "pre";
		String dictCodeHc = "hc";
		String sortField = "sort";

		List<String> result = new ArrayList<>();
		StringBuilder builder;
		String now = DateUtil.now();
		String code, name;
		int sort;
		Set<String> set = new HashSet<>();
		for (JSONObject object : list) {
			name = object.getString(dictName).replaceAll("\\s", "").replaceAll("\\*", "");
			code = object.getString(dictCodePre).replaceAll("\\s", "").replaceAll("\\*", "")
					+ "_"
					+ object.getString(dictCodeHc).replaceAll("\\s", "").replaceAll("\\*", "").replaceAll("\\.0", "");
			sort = object.getInteger(sortField);

			if (set.contains(code+"_"+name)){
				continue;
			}
			set.add(code+"_"+name);

			builder = new StringBuilder();
			builder.append(pre);
			builder.append("'").append(snowflake.nextId()).append("'")
					.append(", ")
					.append("'").append("#dict_type_id").append("'")
					.append(", ")
					.append("'").append(code).append("'")
					.append(", ")
					.append("'").append(name).append("'")
					.append(", ")
					.append("'0'")
					.append(", ")
					.append("'[0]'")
					.append(", ")
					.append("'ENABLE'")
					.append(", ")
					.append("'").append(sort).append("'")
					.append(", ")
					.append("'").append(now).append("'")
					.append(", ")
					.append("'").append(now).append("'");
			builder.append(suf);
			result.add(builder.toString());
		}
		return result;
	}

	// 生成新增字典数据SQL
	private static List<String> createDictSQL(List<MyData> mlList) {
		String dictTypeId = "1749240922725478402";

		String pre = "INSERT INTO `sys_dict`(`dict_id`, `code`, `name`, `parent_id`, `parent_ids`, `sort`, `dict_type_id`, `status`, `create_time`, `create_user`) VALUES (";
		String suf = "'"+ dictTypeId +"', 'ENABLE', '2024-06-14 17:00:00', 1);";

		List<String> sqls = new ArrayList<>();
		int sort = 1;
		StringBuilder sql = null;
		Snowflake snowflake = new Snowflake();
		Set<String> set = new HashSet<>();

		String code, name, sz;
		String tempStr, tempId = null;
		for (MyData gbhy : mlList) {
			sz = gbhy.getSz().trim().split("\\.")[0];
			name = gbhy.getDl().trim();

			if (set.contains(sz+name)) {
				continue;
			}
			set.add(sz+name);

			// 判断 Sort 是否需要从头计算
			if (!Objects.equals(tempId, sz)){
				sort = 1;
				tempId = sz;
			}

			if (sort<10){
				code = sz + "00"+ sort;
			}else if (sort < 100){
				code = sz + "0"+ sort;
			}else {
				code = sz + sort;
			}


			sql = new StringBuilder(pre);
			sql.append("'").append(snowflake.nextIdStr()).append("', ");

			//code
			sql.append("'").append(code).append("', ");
			//name
			sql.append("'").append(name).append("', ");
			//parent_id
			sql.append("'").append(sz).append("', ");
			//parent_ids
			sql.append("'").append("[0],").append("[").append(sz).append("]").append("', ");


			sql.append(sort).append(", ");
			sql.append(suf);
			sqls.add(sql.toString());
			sort++;
		}

		return sqls;
	}

	// 按照下面生成的嵌套关系生成新增数据SQL
	private static List<String> createDictSQLByNesting(List<Nesting> nesting) {
		String dictTypeId = "1749240922725478402";

		String pre = "INSERT INTO `sys_dict`(`dict_id`, `code`, `name`, `parent_id`, `parent_ids`, `sort`, `dict_type_id`, `status`, `create_time`, `create_user`) VALUES (";
		String suf = "'"+ dictTypeId +"', 'ENABLE', '2024-06-14 17:00:00', 1);";

		List<String> sqls = new ArrayList<>();
		StringBuilder sql;

		Queue<Nesting> queue = new LinkedList<>(nesting);
		while (!queue.isEmpty()){
			Nesting poll = queue.poll();
			if (null != poll.getChildren() && !poll.getChildren().isEmpty()){
				queue.addAll(poll.getChildren());
			}

			sql = new StringBuilder(pre);
			sql.append("'").append(poll.getId()).append("', ");

			//code
			String code = poll.getCode();
			sql.append("'").append(code).append("', ");
			//name
			sql.append("'").append(poll.getName()).append("', ");
			//parent_id
			String[] split = poll.getParentCode().split("\\|");
			sql.append("'").append(split[0]).append("', ");
			//parent_ids
			sql.append("'").append(split[1]).append("', ");

			sql.append(Integer.parseInt(code.substring(code.length()-2))).append(", ");
			sql.append(suf);
			sqls.add(sql.toString());
		}

		return sqls;
	}

	// 生成有嵌套关系的对象数组, 每个元素包含: id,code,name,父Id列表,子元素列表
	private static List<Nesting> createNesting(List<MyData> dataList){
		LinkedHashMap<String, LinkedHashMap<String, LinkedHashSet<String>>> mapMap = ReadExcelTest.getStringMapMap(dataList);
		Snowflake snowflake = new Snowflake();

		List<Nesting> szList = new LinkedList<>();
		// 存放值与Code
		Map<String, String> valueMap = new HashMap<>();

		HashSet<String> existName = new HashSet<>();

		int szIndex = 0, dlIndex = 0, xlIndex = 0;
		String szTempCode, dlTempCode, xlTempCode;
		String szId, dlId, xlId;
		List<Nesting> szChildren, dlChildren;
		Nesting tempNest ;

		for (String sz : mapMap.keySet()) {
			// 初始化税种
			sz = sz.trim();
			// 只有不存在才存值
			if (existName.contains(sz)) {
				continue;
			}
			existName.add(sz);
			szIndex++;

			szTempCode = szIndex < 10 ? "10"+szIndex : "1"+szIndex;
			szChildren = new ArrayList<>();
			szId = snowflake.nextIdStr();


			for (String dl : mapMap.get(sz).keySet()) {
				// 初始化大类
				dl = dl.trim();
				if (existName.contains(sz+"-"+dl)) {
					continue;
				}
				existName.add(sz+"-"+dl);
				dlIndex++;

				dlTempCode = szTempCode+(dlIndex < 10 ? "00"+dlIndex : "0"+dlIndex);
				dlChildren = new ArrayList<>();
				dlId = snowflake.nextIdStr();



				for (String xl : mapMap.get(sz).get(dl)) {
					// 初始化小类
					xl = xl.trim();
					if (existName.contains(sz+"-"+dl+"-"+xl)) {
						continue;
					}
					existName.add(sz+"-"+dl+"-"+xl);
					xlIndex++;

					xlTempCode = dlTempCode+(xlIndex < 10 ? "00"+xlIndex : "0"+xlIndex);
					xlId = snowflake.nextIdStr();
					tempNest = new Nesting(xlId, xlTempCode, xl, dlId+"|[0],["+szId+"],["+dlId+"]", null);
					dlChildren.add(tempNest);


					valueMap.put(sz+"-"+dl+"-"+xl, xlTempCode);
				}

				xlIndex = 0;
				tempNest = new Nesting(dlId, dlTempCode, dl, szId+"|[0],["+szId+"]", dlChildren);

				szChildren.add(tempNest);
			}



			dlIndex = 0;
			tempNest = new Nesting(szId, szTempCode, sz, "0|[0]", szChildren);
			szList.add(tempNest);
		}


		System.out.println(JSON.toJSONString(valueMap));
		System.out.println("=======================================================================================");
		System.out.println(valueMap.size());
		System.out.println("=======================================================================================");
		System.out.println("=======================================================================================");
		return szList;
	}
	// 生成有嵌套关系的Map, 目前是 zs->dl->xl 三层
	private static LinkedHashMap<String, LinkedHashMap<String, LinkedHashSet<String>>> getStringMapMap(List<MyData> dataList) {
		// 转换为父子关系
		LinkedHashMap<String, LinkedHashMap<String, LinkedHashSet<String>>> parentChildMap = new LinkedHashMap<>();
		for (MyData data : dataList) {
			String sz = data.getSz().replaceAll("　", "").trim();
			String dl = data.getDl().replaceAll("　", "").trim();
			String xl = data.getXl().replaceAll("　", "").trim();


			// 如果parentChildMap中已经有sz的键，则直接添加dl和xl到对应的列表
			if (parentChildMap.containsKey(sz)) {
				LinkedHashMap<String, LinkedHashSet<String>> dlMap = parentChildMap.get(sz);
				// 如果dlMap中已经有dl的键，则直接添加xl到对应的列表
				if (dlMap.containsKey(dl)) {
					dlMap.get(dl).add(xl);
				} else {
					LinkedHashSet<String> xlList = new LinkedHashSet<>();
					xlList.add(xl);
					dlMap.put(dl, xlList);
				}
			} else {
				// 如果parentChildMap中没有sz的键，则创建一个新的Map，并添加dl和xl
				LinkedHashMap<String, LinkedHashSet<String>> dlMap = new LinkedHashMap<>();
				LinkedHashSet<String> xlList = new LinkedHashSet<>();
				xlList.add(xl);
				dlMap.put(dl, xlList);
				parentChildMap.put(sz, dlMap);
			}
		}
		return parentChildMap;
	}
}
